﻿/*----------------------------------------------------------------
// Copyright (C) 2005 麦迪斯顿(北京)医疗科技发展有限公司
// 文件名：Database.cs
// 文件功能描述：Database
// 创建标识：顾伟伟-2011-01-14
----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Reflection;
using Light.Common.Mapping;
using Light.Data.DataEntity;

namespace Light.Data
{
    /// <summary>
    /// 实现IDatabase的默认抽象基类
    /// </summary>
   public abstract  class Database:IDatabase
    {
        private string _connectionString = string.Empty;

        public Database(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("连接字符串不能为空");
              _connectionString = connectionString;
        }
        /// <summary>
        /// 根据指定的SQL和连接串，创建DbDataAdapter
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="connectionString"></param>
        /// <returns>DbDataAdapter</returns>
        protected virtual DbDataAdapter CreateDbDataAdapter(string commandText, string connectionString)
        {
            throw new NotImplementedException("CreateDbDataAdapter");
        }
        /// <summary>
        /// 根据指定的SQL和DbConnection,创建DbDataAdapter
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        protected virtual DbDataAdapter CreateDbDataAdapter(string commandText, DbConnection connection)
        {
            throw new NotImplementedException("CreateDbDataAdapter");
        }
        /// <summary>
        /// 创建DbDataAdapter
        /// </summary>
        /// <returns>DbDataAdapter</returns>
        protected virtual DbDataAdapter CreateDbDataAdapter()
        {
            throw new NotImplementedException("CreateDbDataAdapter");
        }
        /// <summary>
        /// 根据指定的连接字符串，创建DbConnection
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns>DbConnection</returns>
        protected virtual DbConnection CreateDbConnection(string connectionString)
        {
            throw new NotImplementedException("CreateDbConnection");
        }
        /// <summary>
        /// 创建DbCommandBuilder
        /// </summary>
        /// <returns></returns>
        protected virtual DbCommandBuilder CreateDbCommandBuilder()
        {
            throw new NotImplementedException("CreateDbCommandBuilder");
        }
        /// <summary>
        /// 根据参数名称和类型创建DbParameter
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public virtual DbParameter BuildDbParameter(string parameterName, DbType dbType)
        {
            throw new NotImplementedException("CreateDbParameter");
        }
        /// <summary>
        ///  创建带标志的参数名称
        /// </summary>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public virtual string CreateDbParameterName(string parameterName)
        {
            throw new NotImplementedException("CreateDbParameterName");
        }

        #region IDatabase 成员
        /// <summary>
        /// 根据指定的SQL语句查询数据
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="data">数据表</param>
        /// <returns>受影响的行数</returns>
        public int Fill(string sql, DataTable data)
        {
            return Fill(sql,null,data);
        }
        /// <summary>
        /// 根据指定的SQL语句查询数据
        /// </summary>
        /// <param name="sql">SQL查询</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="data">数据表</param>
        /// <returns>受影响的行数</returns>
        public int Fill(string sql, DbParameter[] parameters, DataTable data)
        {
            using (DbDataAdapter adapter = CreateDbDataAdapter(sql, _connectionString))
            {

                if (parameters != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                }
                try
                {
                    return adapter.Fill(data);
                }
                catch (Exception e)
                {
                   throw new DataAccessException("数据库操作发生异常,请联系系统管理员。",e);
                }

            }
        }
        /// <summary>
        /// 根据指定的SQL语句,当前页码(从零开始)，页大小，分页查询数据
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="pageIndex">当前页码(从零开始)</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="PageCount">总行数</param>
        /// <param name="data">数据表</param>
        /// <returns>总行数</returns>
        public int Fill(string sql, DbParameter[] parameters, int pageIndex, int pageSize, out int totalCount, DataTable data)
        {
            DbConnection connection = CreateDbConnection(_connectionString);
            
            try
            {
                int index=0;
                connection.Open();
                DbCommand command = connection.CreateCommand();

                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                if (parameters != null)
                    command.Parameters.AddRange(parameters);

                int first = pageIndex * pageSize + 1;
                int last = (pageIndex + 1) * pageSize;

                DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var columnName=reader.GetName(i);
                    if (!data.Columns.Contains(columnName))
                    {
                        DataColumn column = new DataColumn();
                        column.ColumnName = reader.GetName(i);
                        column.DataType = reader.GetFieldType(i);
                        data.Columns.Add(column);
                    }
                    
                }
            
                while (reader.Read())
                {
                    index++;
                    if (index >= first && index <= last)
                    {
                       var row= data.NewRow();
                       for (int i = 0; i < reader.FieldCount; i++)
                       {
                           var columnName = reader.GetName(i);
                           row[columnName] = reader[columnName];
                       }
                       
                       data.Rows.Add(row);
                    }
                  
                }

                totalCount = index;
                reader.Close();
                reader.Dispose();
                return index;
            }
            catch (Exception e)
            {
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
                   
            }
            
        }
        /// <summary>
        /// 根据指定的SQL语句,当前页码(从零开始)，页大小，分页查询数据
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="PageCount">总页数</param>
        /// <param name="data">数据源</param>
        /// <returns>总行数</returns>
        public int Fill(string sql, int pageIndex, int pageSize, out int pageCount, DataTable data)
        {
            return Fill(sql, null, pageIndex, pageSize, out pageCount, data);
        }
        /// <summary>
        /// 根据指定的查询命令集，批理查询数据
        /// </summary>
        /// <param name="commands">查询命令集合</param>
        /// <returns>返回受影响的行数</returns>
        public int Fill(DbWrapCommand[] commands)
        {
            using (DbConnection connection = CreateDbConnection(_connectionString))
            {
                int result = 0;
                try
                {
                    connection.Open();
                   
                    using (DbDataAdapter adapter = CreateDbDataAdapter())
                    {
                        adapter.AcceptChangesDuringFill = true;

                        foreach (DbWrapCommand command in commands)
                        {
                            adapter.SelectCommand = connection.CreateCommand();
                            adapter.SelectCommand.CommandType = CommandType.Text;
                            adapter.SelectCommand.CommandText = command.CommandText;

                            if (command.DbParameters != null)
                                adapter.SelectCommand.Parameters.AddRange(command.DbParameters);

                            result += adapter.Fill(command.Data);
                        }

                    }
                    return result;
                }
                catch (Exception e)
                {
                    throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
                }
                finally
                {
                    connection.Close();
                }


            }
        }
        /// <summary>
        /// 执行指定的查询，并返回结果中的第一行，第一列数据
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>结果</returns>
        public object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }
        /// <summary>
        /// 执行指定的查询，并返回结果中的第一行，第一列数据
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">参数</param>
        /// <returns>结果</returns>
        public object ExecuteScalar(string sql, DbParameter[] parameters)
        {
            using (DbConnection connection = CreateDbConnection(_connectionString))
            {
                try
                {
                    connection.Open();
                    DbCommand command = connection.CreateCommand();

                    command.CommandText = sql;
                    command.CommandType = CommandType.Text;

                    if (parameters != null)
                        command.Parameters.AddRange(parameters);

                    object result = command.ExecuteScalar();
                    return result;
                }
                catch (Exception e)
                {
                    throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
                }
                finally
                {
                    connection.Close();
                }
            }

        }
        /// <summary>
        /// 执行指定的SQL语句，并返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null, null);
        }
        /// <summary>
        /// 执行指定的SQL语句，并返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="transaction">事务处理</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql, DbWrapTransaction transaction)
        {
            return ExecuteNonQuery(sql, null, transaction);
        }
        /// <summary>
        /// 执行指定的SQL语句，并返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameter">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql, DbParameter[] parameters)
        {
            return ExecuteNonQuery(sql, parameters, null);
        }
        /// <summary>
        /// 执行指定的SQL语句，并返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="parameter">参数</param>
        /// <param name="transaction">事务</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql, DbParameter[] parameters, DbWrapTransaction transaction)
        {
            DbConnection connection = transaction == null ? CreateDbConnection(_connectionString) : transaction.InnerDbConnection ;
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                DbCommand command = connection.CreateCommand();
                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                if (parameters != null)
                    command.Parameters.AddRange(parameters);

                if (transaction != null)
                    command.Transaction = transaction.InnerDbTransaction 
                        ;
                return command.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                if (transaction == null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteProcedure(string procedureName)
        {
            return ExecuteProcedure(procedureName, null, null);
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteProcedure(string procedureName, DbParameter[] parameters)
        {
            return ExecuteProcedure(procedureName, parameters,null);
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteProcedure(string procedureName, DbParameter[] parameters, DbWrapTransaction transaction)
        {
            DbConnection connection = transaction == null ? CreateDbConnection(_connectionString) : transaction.InnerDbConnection;
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                DbCommand command = connection.CreateCommand();
                command.CommandText = procedureName;
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                    command.Parameters.AddRange(parameters);

                if (transaction != null)
                    command.Transaction = transaction.InnerDbTransaction
                        ;
                return command.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                if (transaction == null)
                {
                    connection.Close();
                    connection.Dispose();
                }

            }
        }
        /// <summary>
        /// 使用存储过程填充表数据
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="data">数据表</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteFillProcedure(string procedureName, DataTable data)
        {
            return ExecuteFillProcedure(procedureName, null, data);
        }
        /// <summary>
        /// 使用存储过程填充表数据
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameters">输入/输出参数</param>
        /// <param name="data">数据表</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteFillProcedure(string procedureName, DbParameter[] parameters, DataTable data)
        {
            using (DbDataAdapter adapter = CreateDbDataAdapter(procedureName, _connectionString))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                }
                try
                {
                    return adapter.Fill(data);
                }
                catch (Exception e)
                {
                    throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
                }

            }
        }
        /// <summary>
        /// 执行指定的存储过程，并返回结果中的第一行，第一列数据
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <returns>返回值</returns>
        public object ExecuteScalarProcedure(string procedureName)
        {
            return ExecuteScalarProcedure(procedureName, null);
        }
        /// <summary>
        /// 执行指定的存储过程，并返回结果中的第一行，第一列数据
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parameters">输入/输出参数</param>
        /// <returns>返回值</returns>
        public object ExecuteScalarProcedure(string procedureName, DbParameter[] parameters)
        {
            using (DbConnection connection = CreateDbConnection(_connectionString))
            {
                try
                {
                    connection.Open();
                    DbCommand command = connection.CreateCommand();

                    command.CommandText = procedureName;
                    command.CommandType = CommandType.StoredProcedure;

                    if (parameters != null)
                        command.Parameters.AddRange(parameters);

                    object result = command.ExecuteScalar();
                    return result;
                }
                catch (Exception e)
                {
                    throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新数据
        /// </summary>
        /// <param name="data">数据表</param>
        /// <returns>受影响的行数</returns>
        public int Update(DataTable data)
        {
            return Update(data, data.TableName);
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新数据
        /// </summary>
        /// <param name="data">数据表</param>
        /// <param name="tableName">数据表名</param>
        /// <returns>受影响的行数</returns>
        public int Update(DataTable data, string tableName)
        {
            string sql = string.Format("select * from {0} where 1=2", tableName);

            using (DbDataAdapter adapter = CreateDbDataAdapter(sql, _connectionString))
            {
                DbCommandBuilder commandBuilder = CreateDbCommandBuilder();
                commandBuilder.DataAdapter = adapter;
                commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                adapter.AcceptChangesDuringUpdate = true;

                try
                {
                    return commandBuilder.DataAdapter.Update(data);
                }
                catch (Exception e)
                {
                    throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
                }
            }
        }
        /// <summary>
        ///  使用事务，根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新数据
        /// </summary>
        /// <param name="data">数据集合</param>
        /// <returns>受影响的行数</returns>
        public int Update(DataTable[] data)
        {
            return Update(data, null);
        }
        /// <summary>
        /// 使用事务，根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新数据
        /// </summary>
        /// <param name="data">数据集合</param>
        /// <param name="tableNames">表名集合</param>
        /// <returns>受影响的行数</returns>
        public int Update(DataTable[] data, string[] tableNames)
        {
            DbConnection connection = CreateDbConnection(_connectionString);
            int result = 0;
            DbTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                using (DbDataAdapter adapter = CreateDbDataAdapter())
                {
                    adapter.AcceptChangesDuringUpdate = true;
                    DbCommandBuilder commandBuilder = CreateDbCommandBuilder();
                    commandBuilder.DataAdapter = adapter;

                    for (int i = 0; i < data.Length; i++)
                    {
                        adapter.SelectCommand = connection.CreateCommand();
                        adapter.SelectCommand.CommandText = string.Format("select * from {0} where 1=2", tableNames != null ? tableNames[i] : data[i].TableName);

                        adapter.SelectCommand.CommandType = CommandType.Text;
                        adapter.SelectCommand.Transaction = transaction;

                        result += commandBuilder.DataAdapter.Update(data[i]);
                    }
                    transaction.Commit();

                }
                return result;
            }
            catch (Exception e)
            {
                if (transaction != null)
                    transaction.Rollback();
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
               
            }
            finally
            {
                if(connection!=null)
                {
                    connection.Close();
                    connection.Dispose();
                }
               if (transaction != null)
                    transaction.Dispose();
            }
            
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand，更新数据源指定列的数据（含主键列）
        /// </summary>
        /// <param name="data">数据表</param>
        /// <param name="columns">需要更新的列名称,含主键列</param>
        /// <returns>受影响的行数</returns>
        public int Update(DataTable data, string[] columns)
        {
            return Update(data, data.TableName, columns);
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand，更新数据源指定列的数据（含主键列）
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="tableName">表名</param>
        /// <param name="columns">需要更新的列名称,含主键列</param>
        /// <returns>受影响的行数</returns>
        public int Update(DataTable data, string tableName, string[] columns)
        {
            return Update(data, tableName, columns, null);
        }
       /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand，更新数据源,并将本次更新纳入到事务中
       /// </summary>
       /// <param name="data">数据</param>
        /// <param name="columns">需要更新的更名（含主键列）</param>
        /// <param name="transaction">事务</param>
       /// <returns></returns>
        public int Update(DataTable data, string[] columns, DbWrapTransaction transaction)
        {
            return Update(data, data.TableName, columns, transaction);
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand，更新数据源,并将本次更新纳入到事务中
        /// </summary>
        /// <param name="data">数据表</param>
        /// <param name="transaction">事务</param>
        /// <returns>受影响的行数</returns>
        public  int Update(DataTable data, DbWrapTransaction transaction)
        {
            return Update(data, data.TableName, transaction); 
        }
       /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand，更新数据源指定列的数据（含主键列）
       /// </summary>
       /// <param name="data">数据表</param>
       /// <param name="tableName">数据表名</param>
       /// <param name="columns">需要更新的更名（含主键列）</param>
       /// <param name="transaction">事务</param>
       /// <returns>受影响的行数</returns>
        public int Update(DataTable data, string tableName, string[] columns, DbWrapTransaction transaction)
        {
            string sql = string.Format("select {1} from {0} where 1=2", tableName, string.Join(",", columns));

            DbDataAdapter adapter = transaction == null ? CreateDbDataAdapter(sql, _connectionString) : CreateDbDataAdapter(sql, transaction.InnerDbConnection);
            adapter.AcceptChangesDuringUpdate = true;

            DbCommandBuilder commandBuilder = CreateDbCommandBuilder();
            commandBuilder.DataAdapter = adapter;
            commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
            if (transaction != null)
            {
                if (commandBuilder.DataAdapter.SelectCommand != null)
                    commandBuilder.DataAdapter.SelectCommand.Transaction = transaction.InnerDbTransaction;
            }
            try
            {
                return commandBuilder.DataAdapter.Update(data);
            }
            catch (Exception e)
            {
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                if (transaction == null)
                {
                    adapter.Dispose();
                }
            }
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新表数据
        /// </summary>
        /// <param name="selectCommandText">DbDataAdapter的初使化SQL</param>
        /// <param name="data">数据</param>
        /// <returns>返回受影响的行数</returns>
        public int Update(string selectCommandText, DataTable data)
        {
            return Update(selectCommandText, data, null);
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新表数据
        /// </summary>
        /// <param name="selectCommandText">DbDataAdapter的初使化SQL</param>
        /// <param name="data">数据</param>
        /// <param name="transaction">事务</param>
        /// <returns>返回受影响的行数</returns>
        public int Update(string selectCommandText, DataTable data, DbWrapTransaction transaction)
        {
            DbDataAdapter adapter = transaction == null ? CreateDbDataAdapter(selectCommandText, _connectionString) : CreateDbDataAdapter(selectCommandText, transaction.InnerDbConnection);
            adapter.AcceptChangesDuringUpdate = true;

            DbCommandBuilder commandBuilder = CreateDbCommandBuilder();
            commandBuilder.DataAdapter = adapter;
            commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
            if (transaction != null)
            {

                if (commandBuilder.DataAdapter.SelectCommand != null)
                    commandBuilder.DataAdapter.SelectCommand.Transaction = transaction.InnerDbTransaction;
            }
            try
            {
                return commandBuilder.DataAdapter.Update(data);
            }
            catch (Exception e)
            {
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                if (transaction == null)
                {
                    adapter.Dispose();
                }
            }
        }

        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新表数据
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="batchSize">执行一次批处理命令时的更新行数</param>
        /// <returns>返回受影响的行数</returns>
        public int Update(DataTable data, int batchSize)
        {
            return Update(data, data.TableName, batchSize, null);
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新表数据
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="tableName">表名</param>
        /// <param name="batchSize">执行一次批处理命令时的更新行数</param>
        /// <returns>返回受影响的行数</returns>
        public int Update(DataTable data, string tableName, int batchSize)
        {
            return Update(data, tableName, batchSize, null);
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新表数据
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="batchSize">执行一次批处理命令时的更新行数</param>
        /// <param name="transaction">事务</param>
        /// <returns>返回受影响的行数</returns>
        public int Update(DataTable data, int batchSize, DbWrapTransaction transaction)
        {
            return Update(data, data.TableName, batchSize, transaction);
        }
        /// <summary>
        /// 根据自动生成的UpdateCommand,DeleteCommand,InsertCommand更新表数据
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="tableName">表名</param>
        /// <param name="batchSize">执行一次批处理命令时的更新行数</param>
        /// <param name="transaction">事务</param>
        /// <returns>返回受影响的行数</returns>
        public int Update(DataTable data, string tableName, int batchSize, DbWrapTransaction transaction)
        {
            string sql = string.Format("select * from {0} where 1=2", tableName);

            DbDataAdapter adapter = transaction == null ? CreateDbDataAdapter(sql, _connectionString) : CreateDbDataAdapter(sql, transaction.InnerDbConnection);
            adapter.AcceptChangesDuringUpdate = true;
            adapter.UpdateBatchSize = batchSize;
            DbCommandBuilder commandBuilder = CreateDbCommandBuilder();
            commandBuilder.DataAdapter = adapter;
            commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
            if (transaction != null)
            {

                if (commandBuilder.DataAdapter.SelectCommand != null)
                    commandBuilder.DataAdapter.SelectCommand.Transaction = transaction.InnerDbTransaction;
            }
            try
            {
                return commandBuilder.DataAdapter.Update(data);
            }
            catch (Exception e)
            {
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                if (transaction == null)
                {
                    adapter.Dispose();
                }
            }
        }
        /// <summary>
        ///  根据自动生成的UpdateCommand,DeleteCommand,InsertCommand，更新数据源,并将本次更新纳入到事务中
        /// </summary>
        /// <param name="data">数据表</param>
        /// <param name="tableName">表名</param>
        /// <param name="transaction">事务</param>
        /// <returns>受影响的行数</returns>
        public int Update(DataTable data, string tableName, DbWrapTransaction transaction)
        {
            string sql = string.Format("select * from {0} where 1=2", tableName);

            return Update(sql, data, transaction);
        }
        /// <summary>
        /// 生成查询参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="value">值</param>
        /// <returns>DbParameter</returns>
        public DbParameter BuildDbParameter(string parameterName, DbType dbType, object value)
        {
            DbParameter parameter = BuildDbParameter(parameterName, dbType);
            parameter.Value = value;
            return parameter;
        }
        /// <summary>
        ///  创建批量查询命令
        /// </summary>
        /// <param name="commandText"查询语句></param>
        /// <param name="data">数据表</param>
        /// <returns>DbFillCommand</returns>
        public DbWrapCommand CreateFillCommand(string commandText, DataTable data)
        {
            return CreateFillCommand(commandText, null, data);
        }
        /// <summary>
        /// 创建批量查询命令
        /// </summary>
        /// <param name="commandText">查询语句</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="data">数据表</param>
        /// <returns>DbFillCommand</returns>
        public DbWrapCommand CreateFillCommand(string commandText, DbParameter[] parameters, DataTable data)
        {
            DbWrapCommand dbFillCommand = new DbWrapCommand(commandText, parameters, data);
            return dbFillCommand;
        }
        /// <summary>
        /// 创建一个事务
        /// </summary>
        /// <returns>DbWrapTransaction</returns>
        public DbWrapTransaction CreateDbTransaction()
        {
            DbWrapTransaction dbWrapTransaction = new DbWrapTransaction(CreateDbConnection(_connectionString));
            return dbWrapTransaction;
        }
        /// <summary>
        /// 根据传入的实体类型和SQL，返回实体的集合
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">查询命令</param>
        /// <returns>实体集合</returns>
        public List<T> Retrieve<T>(string sql) where T : class
        {
            return Retrieve<T>(sql, null);
        }
        /// <summary>
        /// 根据传入的实体类型和SQL，返回实体的集合
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">查询命令</param>
        /// <param name="parameters">查询参数</param>
        /// <returns>实体集合</returns>
        public List<T> Retrieve<T>(string sql, DbParameter[] parameters) where T : class
        {
            var list = new List<T>();

            DataTable data = new DataTable();

            Fill(sql, parameters, data);

            Type entityType=typeof(T);

            var dataMappings=DataEntityContainer.GetEntityMappings(entityType);

            for (int i = 0; i < data.Rows.Count; i++)
            {
                T entity = Activator.CreateInstance<T>();
                foreach(var field in dataMappings.FieldMappings)
                {
                    if (data.Columns.Contains(field.Value.ColumnName))
                    {
                        var fieldValue = data.Rows[i][field.Value.ColumnName];
                        if(fieldValue!=null && fieldValue !=DBNull.Value)
                            field.Key.SetValue(entity, fieldValue, null);
                    }
                   
                }
                list.Add(entity);
             }

            return list;

        }
        /// <summary>
        /// 根据传入的实体类型和SQL，返回实体的集合
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">SQL</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageCount">页数</param>
        /// <param name="totalCount">总行数</param>
        /// <returns>实体集合</returns>
        public List<T> Retrieve<T>(string sql, DbParameter[] parameters, int pageIndex, int pageSize, out int pageCount, out int totalCount) where T : class
        {
            var list = new List<T>();

            DataTable data = new DataTable();

            totalCount = Fill(sql, parameters, pageIndex, pageSize, out pageCount, data);

            Type entityType = typeof(T);

            var dataMappings = DataEntityContainer.GetEntityMappings(entityType);

            for (int i = 0; i < data.Rows.Count; i++)
            {
                T entity = Activator.CreateInstance<T>();
                foreach (var field in dataMappings.FieldMappings)
                {
                    if (data.Columns.Contains(field.Value.ColumnName))
                    {
                        var fieldValue = data.Rows[i][field.Value.ColumnName];
                        if (fieldValue != null && fieldValue != DBNull.Value)
                            field.Key.SetValue(entity, fieldValue, null);
                    }

                }
                list.Add(entity);
            }

            return list;
        }
        /// <summary>
        /// 根据传入的实体类型和SQL，返回实体的集合
        /// </summary>
        // <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">SQL</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageCount">页数</param>
        /// <param name="totalCount">总行数</param>
        /// <returns>实体集合</returns>
        public List<T> Retrieve<T>(string sql, int pageIndex, int pageSize, out int pageCount, out int totalCount) where T : class
        {
            return Retrieve<T>(sql, null, pageIndex, pageSize, out pageCount, out totalCount);
        }
        /// <summary>
        /// 自动生成InsertCommand,UpdateCommand命令，更新实体内容到数据库
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entities">实体集合</param>
        /// <returns>受影响的行数</returns>
        public int Save<T>(List<T> entities) where T : class
        {
            return Save<T>(entities, null);
                
        }
        /// <summary>
        /// 自动生成InsertCommand,UpdateCommand命令，更新实体内容到数据库
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entities">实体集合</param>
        /// <param name="transaction">事务</param>
        /// <returns>受影响的行数</returns>
        public int Save<T>(List<T> entities, DbWrapTransaction transaction) where T:class
        {
            DbConnection connection = transaction == null ? CreateDbConnection(_connectionString) : transaction.InnerDbConnection;
            DbTransaction dbtransaction = null;
         
            try
            {
                int result = 0;
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                if (transaction == null)
                    dbtransaction = connection.BeginTransaction();
                else
                    dbtransaction = transaction.InnerDbTransaction;

                DbCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.Transaction = dbtransaction;

                Type entityType = typeof(T);

                var dataMappings = DataEntityContainer.GetEntityMappings(entityType);
                var entityCommand = DataEntityContainer.GetEntityCommand(entityType,this);

                foreach (var entity in entities)
                {
                    foreach (var field in dataMappings.FieldMappings)
                    {
                        var propertyValue=field.Key.GetValue(entity, null);
                        entityCommand.Parameters[field.Value.ColumnName].Value = propertyValue != null ? propertyValue : DBNull.Value;
                    }

                    command.Parameters.Clear();
                    command.Parameters.AddRange(entityCommand.ToDbParameters());
                    command.CommandText = entityCommand.UpdateCommand;

                    int updateResult = command.ExecuteNonQuery();
                    if (updateResult == 0)
                    {
                        command.CommandText = entityCommand.InsertCommand;
                        command.ExecuteNonQuery();
                    }
                    result++;
                }
                if(transaction==null)
                   dbtransaction.Commit();
                return result;


            }
            catch (Exception e)
            {
                if (transaction == null && dbtransaction != null)
                     dbtransaction.Rollback();
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                if (transaction == null)
                {
                    connection.Close();
                    connection.Dispose();
                }
               
            }
        }
        /// <summary>
        /// 自动生成InsertCommand,UpdateCommand命令，更新实体内容到数据库
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体</param>
        /// <returns>受影响的行数</returns>
        public int Save<T>(T entity) where T : class
        {
            DbConnection connection = CreateDbConnection(_connectionString);

            try
            {
                int result = 0;
                connection.Open();
                DbCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;

                Type entityType = typeof(T);

                var dataMappings = DataEntityContainer.GetEntityMappings(entityType);
                var entityCommand = DataEntityContainer.GetEntityCommand(entityType, this);

                foreach (var field in dataMappings.FieldMappings)
                {
                    var propertyValue = field.Key.GetValue(entity, null);
                    entityCommand.Parameters[field.Value.ColumnName].Value = propertyValue != null ? propertyValue : DBNull.Value;
                }

                command.Parameters.AddRange(entityCommand.ToDbParameters());
                command.CommandText = entityCommand.UpdateCommand;

                result = command.ExecuteNonQuery();
                if (result == 0)
                {
                    command.CommandText = entityCommand.InsertCommand;
                    result = command.ExecuteNonQuery();
                }

                return result;
            }
            catch (Exception e)
            {
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            
        }
        /// <summary>
        /// 自动生成DeleteCommand命令，将实体内容从数据库中删除
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entities">实体集合</param>
        /// <returns>受影响的行数</returns>
        public int Delete<T>(List<T> entities) where T : class
        {
            return Delete<T>(entities, null);
        }
        /// <summary>
        /// 自动生成DeleteCommand命令，将实体内容从数据库中删除
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entities">实体集合</param>
        /// <param name="transaction">事务</param>
        /// <returns>受影响的行数</returns>
        public  int Delete<T>(List<T> entities, DbWrapTransaction transaction) where T : class
        {
            DbConnection connection = transaction == null ? CreateDbConnection(_connectionString) : transaction.InnerDbConnection;
            DbTransaction dbtransaction = null;

            try
            {
                int result = 0;
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                if (transaction == null)
                    dbtransaction = connection.BeginTransaction();
                else
                    dbtransaction = transaction.InnerDbTransaction;

                DbCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.Transaction = dbtransaction;

                Type entityType = typeof(T);

                var dataMappings = DataEntityContainer.GetEntityMappings(entityType);
                var entityCommand = DataEntityContainer.GetEntityCommand(entityType, this);

                foreach (var entity in entities)
                {
                    foreach (var field in dataMappings.FieldMappings)
                    {
                        var propertyValue = field.Key.GetValue(entity, null);
                        entityCommand.Parameters[field.Value.ColumnName].Value = propertyValue != null ? propertyValue : DBNull.Value;
                    }

                    command.Parameters.Clear();
                    command.Parameters.AddRange(entityCommand.ToPrimaryKeyDbParameters());
                    command.CommandText = entityCommand.DeleteCommand;

                    result += command.ExecuteNonQuery();
                   
                }
                if (transaction == null)
                    dbtransaction.Commit();
                return result;


            }
            catch (Exception e)
            {
                if (transaction == null && dbtransaction != null)
                    dbtransaction.Rollback();
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
                if (transaction == null)
                {
                    connection.Close();
                    connection.Dispose();
                }

            }
        }
        /// <summary>
        /// 自动生成DeleteCommand命令，将实体内容从数据库中删除
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体</param>
        /// <returns>受影响的行数</returns>
        public int Delete<T>(T entity) where T : class
        {
            DbConnection connection =  CreateDbConnection(_connectionString) ;
            try
            {
                int result = 0;
                connection.Open();

                DbCommand command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
               
                Type entityType = typeof(T);

                var dataMappings = DataEntityContainer.GetEntityMappings(entityType);
                var entityCommand = DataEntityContainer.GetEntityCommand(entityType, this);

                foreach (var field in dataMappings.FieldMappings)
                {
                    var propertyValue = field.Key.GetValue(entity, null);
                    entityCommand.Parameters[field.Value.ColumnName].Value = propertyValue != null ? propertyValue : DBNull.Value;
                }

                command.Parameters.Clear();
                command.Parameters.AddRange(entityCommand.ToPrimaryKeyDbParameters());
                command.CommandText = entityCommand.DeleteCommand;

                result += command.ExecuteNonQuery();

                return result;


            }
            catch (Exception e)
            {
                throw new DataAccessException("数据库操作发生异常,请联系系统管理员。", e);
            }
            finally
            {
               connection.Close();
               connection.Dispose();
            }
        }
       
        
        #endregion





        
    }
}
